package com.muk.meis.model.service;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.w3c.dom.Element;
import org.w3c.dom.Text;

import com.muk.meis.model.bean.School;
import com.muk.meis.model.bean.SchoolInfo;
import com.muk.meis.util.XMLUtil;

public class SchoolService extends BaseMEISService {

	public static List<School> getList() {
		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory("meis");
		EntityManager em = emf.createEntityManager();
		List<School> lst = em.createQuery("select e from School e")
				.getResultList();
		return lst;
	}
	
	public static String getSchoolNameByPerCode(String perCode){
		EntityManagerFactory emf = Persistence
				.createEntityManagerFactory("meis");
		EntityManager em = emf.createEntityManager();
		School school = em.find(School.class, perCode);
		return school.getSchoolName();
	}
	
	public int getDistrictByPercode(String percode){
		SchoolInfo schoolInfo = em.createQuery("select b from School a, SchoolInfo b"
				+ "     where a.percodeCode = b.percodeCode"
				+ "      and a.percodeCode =  '" + percode + "'",SchoolInfo.class).getSingleResult();
		return schoolInfo.getDistrictId();
	}
	
	public String getXMLSchoolReport(String percode){
		School school = em.createQuery("select e from School e where e.percodeCode = '"+percode+"'",School.class).getSingleResult();
		if(school == null) return null;
		String templateStr = "<chart>" 
		        + "<series>"
				+ "<value xid='0'>อ.1</value>"
				+ "<value xid='1'>อ.2</value>"
				+ "<value xid='2'>ป.1</value>"
				+ "<value xid='3'>ป.2</value>"
				+ "<value xid='4'>ป.3</value>"
				+ "<value xid='5'>ป.4</value>"
				+ "<value xid='6'>ป.5</value>"
				+ "<value xid='7'>ป.6</value>"
				+ "<value xid='8'>ม.1</value>"
				+ "<value xid='9'>ม.2</value>"
				+ "<value xid='10'>ม.3</value>" 
				+"</series>"  
				+"<graphs>"
				+ "<graph gid='0'>" 
				+ "<value xid='0'>"+school.getKindergartenMan1()+"</value>"
				+ "<value xid='1'>"+school.getKindergartenMan2()+"</value>"
				+ "<value xid='2'>"+school.getPrimaryEducationMan1()+"</value>"
				+ "<value xid='3'>"+school.getPrimaryEducationMan2()+"</value>"
				+ "<value xid='4'>"+school.getPrimaryEducationMan3()+"</value>"
				+ "<value xid='5'>"+school.getPrimaryEducationMan4()+"</value>"
				+ "<value xid='6'>"+school.getPrimaryEducationMan5()+"</value>"
				+ "<value xid='7'>"+school.getPrimaryEducationMan6()+"</value>"
				+ "<value xid='8'>"+school.getHighSchoolMan1()+"</value>"
				+ "<value xid='9'>"+school.getHighSchoolMan2()+"</value>"
				+ "<value xid='10'>"+school.getHighSchoolMan3()+"</value>" 
				+ "</graph>"
				+ "<graph gid='1'>" 
				+ "<value xid='0'>"+school.getKindergartenGirl1()+"</value>"
				+ "<value xid='1'>"+school.getKindergartenGirl2()+"</value>"
				+ "<value xid='2'>"+school.getHighSchoolGirl1()+"</value>"
				+ "<value xid='3'>"+school.getHighSchoolGirl2()+"</value>"
				+ "<value xid='4'>"+school.getHighSchoolGirl3()+"</value>"
				+ "<value xid='5'>"+school.getHighSchoolGirl4()+"</value>"
				+ "<value xid='6'>"+school.getHighSchoolGirl5()+"</value>"
				+ "<value xid='7'>"+school.getHighSchoolGirl6()+"</value>"
				+ "<value xid='8'>"+school.getHighSchoolGirl1()+"</value>"
				+ "<value xid='9'>"+school.getHighSchoolGirl2()+"</value>"
				+ "<value xid='10'>"+school.getHighSchoolGirl3()+"</value>" 
				+ "</graph>" 
				+ "</graphs>"
				+ "</chart>";
		return templateStr;
	}

	public String getXMLDistrict(String perCode){
		Object[] obj = (Object[]) em.createQuery(
				"select sum(e.kindergartenMan1),"
						+ "       sum(e.kindergartenMan2),"
						+ "       sum(e.primaryEducationMan1),"
						+ "       sum(e.primaryEducationMan2),"
						+ "       sum(e.primaryEducationMan3),"
						+ "       sum(e.primaryEducationMan4),"
						+ "       sum(e.primaryEducationMan5),"
						+ "       sum(e.primaryEducationMan6),"
						+ "       sum(e.highSchoolMan1),"
						+ "       sum(e.highSchoolMan2),"
						+ "       sum(e.highSchoolMan3),"
						+ "        sum(e.kindergartenGirl1),"
						+ "       sum(e.kindergartenGirl2),"
						+ "       sum(e.primaryEducationGirl1),"
						+ "       sum(e.primaryEducationGirl2),"
						+ "       sum(e.primaryEducationGirl3),"
						+ "       sum(e.primaryEducationGirl4),"
						+ "       sum(e.primaryEducationGirl5),"
						+ "       sum(e.primaryEducationGirl6),"
						+ "       sum(e.highSchoolGirl1),"
						+ "       sum(e.highSchoolGirl2),"
						+ "       sum(e.highSchoolGirl3),"
						+ "        info.districtId"
						+ "   from School e, SchoolInfo info"
						+ "    where e.percodeCode = info.percodeCode "
						+ "          and info.districtId = " + getDistrictByPercode(perCode)
						+ "    group by 23 ")
				.getSingleResult();
		if(obj == null) return null;
		String templateStr = "<chart>" 
		        + "<series>"
				+ "<value xid='0'>อ.1</value>"
				+ "<value xid='1'>อ.2</value>"
				+ "<value xid='2'>ป.1</value>"
				+ "<value xid='3'>ป.2</value>"
				+ "<value xid='4'>ป.3</value>"
				+ "<value xid='5'>ป.4</value>"
				+ "<value xid='6'>ป.5</value>"
				+ "<value xid='7'>ป.6</value>"
				+ "<value xid='8'>ม.1</value>"
				+ "<value xid='9'>ม.2</value>"
				+ "<value xid='10'>ม.3</value>" 
				+"</series>"  
				+"<graphs>"
				+ "<graph gid='0'>" 
				+ "<value xid='0'>"+obj[0]+"</value>"
				+ "<value xid='1'>"+obj[1]+"</value>"
				+ "<value xid='2'>"+obj[2]+"</value>"
				+ "<value xid='3'>"+obj[3]+"</value>"
				+ "<value xid='4'>"+obj[4]+"</value>"
				+ "<value xid='5'>"+obj[5]+"</value>"
				+ "<value xid='6'>"+obj[6]+"</value>"
				+ "<value xid='7'>"+obj[7]+"</value>"
				+ "<value xid='8'>"+obj[8]+"</value>"
				+ "<value xid='9'>"+obj[9]+"</value>"
				+ "<value xid='10'>"+obj[10]+"</value>" 
				+ "</graph>"
				+ "<graph gid='1'>" 
				+ "<value xid='0'>"+obj[11]+"</value>"
				+ "<value xid='1'>"+obj[12]+"</value>"
				+ "<value xid='2'>"+obj[13]+"</value>"
				+ "<value xid='3'>"+obj[14]+"</value>"
				+ "<value xid='4'>"+obj[15]+"</value>"
				+ "<value xid='5'>"+obj[16]+"</value>"
				+ "<value xid='6'>"+obj[17]+"</value>"
				+ "<value xid='7'>"+obj[18]+"</value>"
				+ "<value xid='8'>"+obj[19]+"</value>"
				+ "<value xid='9'>"+obj[20]+"</value>"
				+ "<value xid='10'>"+obj[21]+"</value>" 
				+ "</graph>" 
				+ "</graphs>"
				+ "</chart>";
		return templateStr;
	}
	
	public String getXMLALLMuk(){
		Object[] obj = (Object[]) em.createQuery(
				"select sum(e.kindergartenMan1),"
						+ "       sum(e.kindergartenMan2),"
						+ "       sum(e.primaryEducationMan1),"
						+ "       sum(e.primaryEducationMan2),"
						+ "       sum(e.primaryEducationMan3),"
						+ "       sum(e.primaryEducationMan4),"
						+ "       sum(e.primaryEducationMan5),"
						+ "       sum(e.primaryEducationMan6),"
						+ "       sum(e.highSchoolMan1),"
						+ "       sum(e.highSchoolMan2),"
						+ "       sum(e.highSchoolMan3),"
						+"        sum(e.kindergartenGirl1),"
						+ "       sum(e.kindergartenGirl2),"
						+ "       sum(e.primaryEducationGirl1),"
						+ "       sum(e.primaryEducationGirl2),"
						+ "       sum(e.primaryEducationGirl3),"
						+ "       sum(e.primaryEducationGirl4),"
						+ "       sum(e.primaryEducationGirl5),"
						+ "       sum(e.primaryEducationGirl6),"
						+ "       sum(e.highSchoolGirl1),"
						+ "       sum(e.highSchoolGirl2),"
						+ "       sum(e.highSchoolGirl3)"						
						+ "   from School e")
				.getSingleResult();
		if(obj == null) return null;
		String templateStr = "<chart>" 
		        + "<series>"
				+ "<value xid='0'>อ.1</value>"
				+ "<value xid='1'>อ.2</value>"
				+ "<value xid='2'>ป.1</value>"
				+ "<value xid='3'>ป.2</value>"
				+ "<value xid='4'>ป.3</value>"
				+ "<value xid='5'>ป.4</value>"
				+ "<value xid='6'>ป.5</value>"
				+ "<value xid='7'>ป.6</value>"
				+ "<value xid='8'>ม.1</value>"
				+ "<value xid='9'>ม.2</value>"
				+ "<value xid='10'>ม.3</value>" 
				+"</series>"  
				+"<graphs>"
				+ "<graph gid='0'>" 
				+ "<value xid='0'>"+obj[0]+"</value>"
				+ "<value xid='1'>"+obj[1]+"</value>"
				+ "<value xid='2'>"+obj[2]+"</value>"
				+ "<value xid='3'>"+obj[3]+"</value>"
				+ "<value xid='4'>"+obj[4]+"</value>"
				+ "<value xid='5'>"+obj[5]+"</value>"
				+ "<value xid='6'>"+obj[6]+"</value>"
				+ "<value xid='7'>"+obj[7]+"</value>"
				+ "<value xid='8'>"+obj[8]+"</value>"
				+ "<value xid='9'>"+obj[9]+"</value>"
				+ "<value xid='10'>"+obj[10]+"</value>" 
				+ "</graph>"
				+ "<graph gid='1'>" 
				+ "<value xid='0'>"+obj[11]+"</value>"
				+ "<value xid='1'>"+obj[12]+"</value>"
				+ "<value xid='2'>"+obj[13]+"</value>"
				+ "<value xid='3'>"+obj[14]+"</value>"
				+ "<value xid='4'>"+obj[15]+"</value>"
				+ "<value xid='5'>"+obj[16]+"</value>"
				+ "<value xid='6'>"+obj[17]+"</value>"
				+ "<value xid='7'>"+obj[18]+"</value>"
				+ "<value xid='8'>"+obj[19]+"</value>"
				+ "<value xid='9'>"+obj[20]+"</value>"
				+ "<value xid='10'>"+obj[21]+"</value>" 
				+ "</graph>" 
				+ "</graphs>"
				+ "</chart>";
		return templateStr;
	}
	
	public static void main(String[] args) {
		SchoolService service = new SchoolService();
		System.out.println(service.getXMLSchoolReport("730062"));
	}
	
	public static List<String> getListTop5PerCode(){
		EntityManagerFactory emf = Persistence.createEntityManagerFactory("meis");
		EntityManager em = emf.createEntityManager();
		List lst = em.createQuery("select e.percodeCode, sum(e.kindergartenMan1+e.kindergartenGirl1+"+
                          "e.kindergartenMan2+e.kindergartenGirl2+"+
                          "e.kindergartenMan3+e.kindergartenGirl3+"+
                          "e.primaryEducationMan1+e.primaryEducationGirl1+"+
                          "e.primaryEducationMan2+e.primaryEducationGirl2+"+
                          "e.primaryEducationMan3+e.primaryEducationGirl3+"+
                          "e.primaryEducationMan4+e.primaryEducationGirl4+"+
                          "e.primaryEducationMan5+e.primaryEducationGirl5+"+
                          "e.primaryEducationMan6+e.primaryEducationGirl6+"+
                          "e.highSchoolMan1+e.highSchoolGirl1+"+
                          "e.highSchoolMan2+e.highSchoolGirl2+"+
                          "e.highSchoolMan3+e.highSchoolGirl3+"+
                          "e.highSchoolMan4+e.highSchoolGirl4+"+
                          "e.highSchoolMan5+e.highSchoolGirl5+"+
                          "e.highSchoolMan6+e.highSchoolGirl6"+
                          ") "+
						  "from School e "+
						  "group by e.percodeCode "+
						  "order by 2 desc")
				.setMaxResults(5).getResultList();
		List<String> lstString = new ArrayList<String>();
		for (Iterator iterator = lst.iterator(); iterator.hasNext();) {
			Object[] object = (Object[]) iterator.next();
			lstString.add((String)object[0]);
		}
		return lstString;
	}

}
